Release 10.1A: OpenEdge Data Management:
SQL Development

How the query optimizer works

The optimizer works not only on statements reading data, but also on statements writing data. For any SQL statement, there are many possible methods to compute results. The optimizer decides which methods to use, the order in which to apply the methods, and the characteristics of each method. The optimization model used by the OpenEdge SQL Engine is a synthesis of:

Decomposition — Statements are broken into elementary pieces such as tables, columns, and predicates.
Relational algebra operations — This includes operations such as project, restrict, join, and sort.
Composition — Primitive operations, such as restrict or join, are composed into a sequence of steps.
Cost-based analysis and decision making— Alternative operations are cost estimated, and the least costly operation is chosen.
Rule-based analysis and decision making— Rules expressing proven, efficient statement execution methods determine how operations and their attributes are built and combined.

Representing the statement as a query tree

The query processor makes extensive use of a relational algebra tree representation to model and manipulate SQL queries. At various points within the tree, operations are performed on the data. Each operation is represented as a node in the tree. Nodes can have one or more expressions associated with them to specify columns, conditions, and calculations associated with the operation.

Some of the operators that might be present in the tree are:

Restrict — Reduces the number of output rows by eliminating those that fail to satisfy some condition applied to the input. Restrict operators appear in the tree from WHERE clauses and JOINs.
Project — Reduces the number of output columns by eliminating columns not present in a project list. Projection operators appear in the tree from SELECT statements, from the list of columns needed for a table, and for aggregations such as SUM.
Join — Combines two input tables into a single output table that contains some combination of rows from the inputs. Joins appear in the tree from the use of FROM clauses and from JOIN clauses.
Sort — Changes the ordering of rows in an input table to produce an output table in the desired order.
Table — Represents a table scan or an index scan, reading data from a given table by either its default index (table scan) or a specific index (index scan).

Leaf nodes of the tree are always references to database tables. The following diagram illustrates a tree produced for the query:

SELECT Name, OrderDate FROM Customer, Order WHERE Order."OrderDate" = Order."ShipDate";

This query lists the names and order dates for all customers whose orders were shipped on the same day the order was placed: